﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace TestLogin
{
	class DBHandler
	{
	//static	
	static	public void addAccount(string username,string password)
		{

			string query = "";// "SELECT * FROM Login WHERE Username LIKE @";
			string connectStr = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=C:\\Program Files (x86)\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\LoginTestDB.mdf;Integrated Security=True;Connect Timeout=30";//*///"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\\Users\\SPK10nguyen\\Documents\\Visual Studio 2013\\Projects\\TestLogin\\TestLogin\\TestDBSLoginNigo.mdf;Integrated Security=True";
			#region using connect
			using (SqlConnection connect = new SqlConnection(connectStr))
			{
				try
				{

					connect.Open();
					//if(connect.State != ConnectionState.Open)
						System.Windows.Forms.MessageBox.Show("Connection state = " + connect.State);
					SqlCommand command = new SqlCommand("RegisterAccount", connect);
					command.CommandType = CommandType.StoredProcedure;
					command.Parameters.Add("@Username", username);
					command.Parameters.Add("@Password", password);
					command.Parameters.Add("@AccessLevel", 5);

					command.ExecuteNonQuery();
					//*/

					#region using command
					/*
					using (SqlCommand command = new SqlCommand(query, connect))
					{
						try
						{
							

						}
						catch (InvalidOperationException)
						{
							//log and/or rethrow or ignore
						}
						catch (SqlException)
						{
							query = "SqlException";
							//log and/or rethrow or ignore
						}
						catch (ArgumentException)
						{
							query = "ArgumentException";
							//log and/or rethrow or ignore
						}
					}
					 * 
					 //*/
					#endregion
				}
				//*/
				#region catch InvalidOperationException
				catch (InvalidOperationException)
				{
					query = "InvalidOperationException";
					//log and/or rethrow or ignore
				}
				#endregion
				#region catch SqlException
				catch (SqlException)
				{
					query = "SqlException";
					//log and/or rethrow or ignore
				}
				#endregion
				#region ArgumentException
				catch (ArgumentException)
				{
					query = "ArgumentException";
					//log and/or rethrow or ignore
				}
				#endregion
			}
			#endregion
		}
	static	public int login(string username, string password)
		{
			//string userName = this.UserTB.Text;
			//string password = this.PassTB.Text;
			int accessLevel = -1;
			string query = "";// "SELECT * FROM Login WHERE Username LIKE @";
			string connectStr = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=C:\\Program Files (x86)\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\LoginTestDB.mdf;Integrated Security=True;Connect Timeout=30";//*///"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\\Users\\SPK10nguyen\\Documents\\Visual Studio 2013\\Projects\\TestLogin\\TestLogin\\TestDBSLoginNigo.mdf;Integrated Security=True";

			#region using connect
			using (SqlConnection connect = new SqlConnection(connectStr))
			{
				try
				{

					connect.Open();
					
					SqlCommand command = new SqlCommand("GetAccessLevel", connect);
					
					
					
					System.Windows.Forms.MessageBox.Show("Connection state = " + connect.State);

					command.CommandType = CommandType.StoredProcedure;
					command.Parameters.Add("@Username", username);
					command.Parameters.Add("@Password", password);
					
					/*
					accessLevel = (Int32)command.ExecuteScalar();
					//*/

					SqlDataReader dr = command.ExecuteReader();
					while (dr.Read())
					{

						query = dr[0].ToString();
						accessLevel = int.Parse(	dr[0].ToString());
					}
					//get access level from SqlCommand aka Select thingy

					//*/

					#region using command
					/*
					using (SqlCommand command = new SqlCommand(query, connect))
					{
						try
						{
							

						}
						catch (InvalidOperationException)
						{
							//log and/or rethrow or ignore
						}
						catch (SqlException)
						{
							query = "SqlException";
							//log and/or rethrow or ignore
						}
						catch (ArgumentException)
						{
							query = "ArgumentException";
							//log and/or rethrow or ignore
						}
					}
					 * 
					 //*/
					#endregion
				}
				//*/
				#region catch InvalidOperationException
				catch (InvalidOperationException)
				{
					query = "InvalidOperationException";
					//log and/or rethrow or ignore
				}
				#endregion
				#region catch SqlException
				catch (SqlException)
				{
					query = "SqlException";
					//log and/or rethrow or ignore
				}
				#endregion
				#region ArgumentException
				catch (ArgumentException)
				{
					query = "ArgumentException";
					//log and/or rethrow or ignore
				}
				#endregion
			}
			#endregion
			return accessLevel;

		}
	static public void cleanse()
		{
			string query = "";
			string connectStr = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=C:\\Program Files (x86)\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\LoginTestDB.mdf;Integrated Security=True;Connect Timeout=30";//*///"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\\Users\\SPK10nguyen\\Documents\\Visual Studio 2013\\Projects\\TestLogin\\TestLogin\\TestDBSLoginNigo.mdf;Integrated Security=True";
			#region using connect
			using (SqlConnection connect = new SqlConnection(connectStr))
			{
				try
				{

					connect.Open();
					SqlCommand command = new SqlCommand("CleanseAccounts", connect);
					System.Windows.Forms.MessageBox.Show("Connection state = " + connect.State);
					command.CommandType = CommandType.StoredProcedure;
					command.ExecuteNonQuery();
				}
				//*/
				#region catch InvalidOperationException
				catch (InvalidOperationException)
				{
					query = "InvalidOperationException";
					//log and/or rethrow or ignore
				}
				#endregion
				#region catch SqlException
				catch (SqlException)
				{
					query = "SqlException";
					//log and/or rethrow or ignore
				}
				#endregion
				#region ArgumentException
				catch (ArgumentException)
				{
					query = "ArgumentException";
					//log and/or rethrow or ignore
				}
				#endregion
			}
			#endregion


		}
	}
}
